USE master;
GO

SET NOCOUNT ON;
GO

PRINT 'I/O statistics by file for all databases';
SELECT
	DB_NAME(vfs.database_id) AS database_name,
	vfs.database_id,
	vfs.file_id,
	vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_latency_ms,
	vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_latency_ms,
	io_stall / NULLIF(vfs.num_of_reads + vfs.num_of_writes, 0) AS avg_total_latency_ms,
	vfs.num_of_bytes_read / NULLIF(vfs.num_of_reads, 0) AS avg_bytes_per_read,
	vfs.num_of_bytes_written / NULLIF(vfs.num_of_writes, 0) AS avg_bytes_per_write,
	vfs.io_stall AS io_stall_ms,
	vfs.num_of_reads,
	vfs.num_of_bytes_read,
	vfs.io_stall_read_ms,
	vfs.num_of_writes,
	vfs.num_of_bytes_written,
	vfs.io_stall_write_ms,
	CAST(vfs.size_on_disk_bytes / (POWER(1024, 2)) AS DECIMAL(12, 2)) AS size_on_disk_mb,
	mf.physical_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
ORDER BY avg_total_latency_ms DESC OPTION (RECOMPILE);

PRINT 'pending I/O requests for all databases';
SELECT
	DB_NAME(vfs.database_id) AS database_name,
	mf.name AS file_name,
	mf.type_desc AS file_type,
	pir.io_type,
	pir.io_offset,
	pir.io_pending_ms_ticks
FROM sys.dm_io_pending_io_requests AS pir WITH (NOLOCK)
INNER JOIN sys.dm_io_virtual_file_stats (NULL, NULL) AS vfs
	ON vfs.file_handle = pir.io_handle
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
	ON mf.database_id = vfs.database_id
	AND mf.file_id = vfs.file_id
WHERE pir.io_pending = 1
ORDER BY pir.io_offset ASC OPTION (RECOMPILE);